﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace ncp.Business
{
    public class NewsInfoBll
    {
        /// <summary>
        /// 得到新闻信息表
        /// </summary>
        public static DataTable GetNewsInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select * from bi_NewsInfo_v";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }
        /// <summary>
        /// 得到新闻信息表
        /// </summary>
        public static  DataTable GetNewsInfoTable(string fieldList,Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            string sqlStr = "select " + fieldList + " from bi_NewsInfo_v where " + pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到新闻信息表
        /// </summary>
        public static DataTable GetNewsInfoViewTable(string fieldList, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            string sqlStr = "select " + fieldList + " from bi_NewsInfo_v where " + pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到新闻信息表(按VIp，用户积分，新闻发布时间排序)
        /// isSingle:是否一个客户只取一条记录
        /// </summary>
        public static DataTable GetNewsInfoTable(string fieldList, Dictionary<string, object> dicWhere, string order, string pDataBase,bool isSingle)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = " ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key!="more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            StringBuilder builder=new StringBuilder();
            builder.AppendFormat(" select {0} from ",fieldList);
            if (order.IndexOf("admin")!=-1)
            {
                
                builder.AppendFormat(" bi_NewsInfo_v t where 1=1 {0}", pcWhere);
                if (order=="admin")
                {
                    builder.Append(" order by  News_Date desc");
                }
                else
                {
                    builder.Append(" order by "+order.Split('|')[1]);
                }
            }
            else
            {
                builder.Append(" bi_NewsInfo_v t where 1=1");
                if (isSingle)
                {
                    builder.AppendFormat(" and not exists (select 1 from bi_NewsInfo_v where client_id=t.client_id and News_Id<t.News_Id {0} ) ", pcWhere);
                }
                builder.Append(pcWhere);

                builder.AppendFormat(" order by case when Vip_EndDate is null or Vip_EndDate<getdate() then '1900-1-1' end,User_Points,{0} News_Date desc", order);
            }

            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(builder.ToString(), pDataBase, paras);
        }
       
        /// <summary>
        /// 得到新闻信息表
        /// </summary>
        /// <param name="fieldList">查询字段</param>
        /// <param name="sqlWhere">带参数的sql</param>
        /// <param name="paras">附加sql中所带参数声明</param>
        /// <param name="join">需要jion的表(无外链表留空)</param>
        /// <param name="pDataBase">数据库</param>
        /// <returns></returns>
        public static DataTable GetNewsInfoTable(string fieldList, string sqlWhere,string join, SqlParameter[] paras, string pDataBase)
        {
//            List<SqlParameter> ilist = new List<SqlParameter>();
            string sqlStr = "select " + fieldList + " from bi_NewsInfo "+join+" where " + sqlWhere;
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetNewsInfoJoinTable(string fieldList,string orderBy,string sqlWhere,SqlParameter[] paras,string pDataBase)
        {
            string sqlStr = " select " + fieldList +" from bi_NewsInfo as a inner join bi_ClientInfo as b on a.Client_Id=b.Client_Id " +sqlWhere+" "+orderBy;
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到分页新闻信息表
        /// </summary>
        public static DataTable GetNewsInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
　　        {
　　            if (kvp.Key!="more")
　　            {
　　                pcWhere +=" and "+ kvp.Key + "=@" + kvp.Key;
                 ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
　　            }
　　            else
　　            {
　　                pcWhere += " and " + kvp.Value;
　　            }
　　        }
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_NewsInfo_v", pcFieldList, "News_Id", pcOrderBy, pcWhere);
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetNewsInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, string pcWhere, string p_DataBase)
        {
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_NewsInfo_v", pcFieldList, "News_Id", pcOrderBy, pcWhere);
            return CommonBll.GetdtList(sqlStr, p_DataBase);
        }

        /// <summary>
        /// 根据ID得到新闻记录
        /// </summary>
        public static DataTable GetNewsById(string id,string userId,string pDataBase)
        {
            string sqlStr = "select * from bi_NewsInfo where News_Id=@newsId and Client_Id=@clientId";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@newsId", id),
                                           new SqlParameter("@clientId",userId), 
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到新闻记录数
        /// </summary>
        public static int GetNewsInfoCount(string pcWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from bi_NewsInfo_v";
            if (!string.IsNullOrEmpty(pcWhere))
                sqlStr += " where " + pcWhere;
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }

        public static int GetNewsInfoCount(Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = " where 1=1 ";
            string sqlStr = "select count(*) from bi_NewsInfo_v";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;

                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            SqlParameter[] paras = ilist.ToArray();
            return Convert.ToInt32(CommonBll.GetdtList( sqlStr + pcWhere, pDataBase, paras).Rows[0][0]);
        }

        /// <summary>
        /// 修改新闻信息
        /// </summary>
        public static int UpdateNewsInfo(Dictionary<string, object> dicNews, string pDataBase)
        {
            string sqlStr = "UPDATE dbo.bi_NewsInfo " +
                            "SET Client_Id =@client_id, " +
                            "News_Title = @news_title, " +
                            "News_Note =  @news_note," +
                            "NC_Code =  @nc_code,News_Picture=@news_picture,News_Description=@News_Description,News_Flag=@News_Flag,user_id=@user_id,News_StartDate=@News_StartDate,News_EndDate=@News_EndDate,News_Addr=@News_Addr,News_Code=@News_Code,News_Source=@source " +
                            "where News_Id=@news_id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@client_id", dicNews.ContainsKey("Client_Id")?dicNews["client_id"]:0),
                                           new SqlParameter("@news_title", dicNews["newsTitle"]),
                                           new SqlParameter("@news_note", dicNews["newsContent"]),
                                           new SqlParameter("@nc_code", dicNews["newsType"]),
                                           new SqlParameter("@news_picture", dicNews["newsPic"]),
                                           new SqlParameter("@news_id",dicNews["newsId"]),
                                           new SqlParameter("@News_Description",dicNews["description"]),
                                           new SqlParameter("@News_StartDate",  dicNews.ContainsKey("news_startdate")?dicNews["news_startdate"]:null),
                                           new SqlParameter("@News_EndDate",  dicNews.ContainsKey("news_enddate")?dicNews["news_enddate"]:null),
                                           new SqlParameter("@News_Addr",  dicNews.ContainsKey("news_addr")?dicNews["news_addr"]:""),
                                           new SqlParameter("@News_Code",  dicNews.ContainsKey("news_code")?dicNews["news_code"]:""),
                                           new SqlParameter("@user_id", dicNews.ContainsKey("user_id")?dicNews["user_id"]:0),
                                           new SqlParameter("@News_Flag",dicNews.ContainsKey("News_Flag")?dicNews["News_Flag"]:""),
                                           new SqlParameter("@source",dicNews["source"])
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        public static int UpdateNewsInfo(Dictionary<string, object> dicNews, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            string fields = "";
            if (dicNews.Count==0)
            {
                return 0;
            }
            foreach (KeyValuePair<string, object> kvp in dicNews)
            {
                fields += "," + kvp.Key+"=@"+kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            fields = fields.Substring(1);
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;

                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            string sqlStr = "update bi_NewsInfo set " + fields +" where "+ pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 增加新闻信息
        /// </summary>
        public static int InsertNewsInfo(Dictionary<string, object> dicNews, string pDataBase)
        {
            string sqlStr = "INSERT INTO dbo.bi_NewsInfo (Client_Id, News_Title, News_Note, News_Date, News_Times, NC_Code, News_Status,News_Picture,News_Description,News_Flag,user_id,News_StartDate,News_EndDate,News_Addr,News_Code";
            string declare = ")";
            if (dicNews.ContainsKey("source"))
            {
                sqlStr += ",News_Source";
                declare = ",@source)";
            }
            sqlStr += ") VALUES (@client_id, @news_title, @news_note, getdate(), 0, @nc_code, 1,@news_picture,@News_Description,@News_Flag,@user_id,@News_StartDate,@News_EndDate,@News_Addr,@News_Code" + declare;
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@client_id", dicNews.ContainsKey("client_id")?dicNews["client_id"]:0),
                                           new SqlParameter("@news_title", dicNews["newsTitle"]),
                                           new SqlParameter("@news_note", dicNews["newsContent"]),
                                           new SqlParameter("@nc_code", dicNews["newsType"]),
                                           new SqlParameter("@news_picture", dicNews["newsPic"]),
                                           new SqlParameter("@News_Description", dicNews["description"]),
                                           new SqlParameter("@News_StartDate",  dicNews.ContainsKey("news_startdate")?dicNews["news_startdate"]:null),
                                           new SqlParameter("@News_EndDate",  dicNews.ContainsKey("news_enddate")?dicNews["news_enddate"]:null),
                                           new SqlParameter("@News_Addr",  dicNews.ContainsKey("news_addr")?dicNews["news_addr"]:""),
                                           new SqlParameter("@News_Code",  dicNews.ContainsKey("news_code")?dicNews["news_code"]:""),
                                           new SqlParameter("@user_id", dicNews.ContainsKey("user_id")?dicNews["user_id"]:0),
                                           new SqlParameter("@News_Flag",dicNews.ContainsKey("News_Flag")?dicNews["News_Flag"]:""),
                                           new SqlParameter("@source", dicNews.ContainsKey("source")?dicNews["source"]:"")
                                       };

            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 删除新闻
        /// </summary>
        public static int DeleteNewsInfo(string News_Id, string pDataBase)
        {
            string sqlStr = "delete from bi_NewsInfo where News_Id in(" + News_Id + ")";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }
        /// <summary>
        /// 删除新闻
        /// </summary>
        public static int DeleteNewsInfo(string News_Id,string clientId, string pDataBase)
        {
            string sqlStr = "delete from bi_NewsInfo where News_Id in(" + News_Id + ") and Client_Id=" + clientId;
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        //获取文章分类
        public static DataTable GetNewsType(string type, string pDataBase)
        {
            string sql = "select * from bi_NewsClass where NC_Status=1 and nc_type="+type;
            return CommonBll.GetdtList(sql, pDataBase);
        }

        //增加点击次数
        public static int PageClicked(string newsId, string pDataBase)
        {
            string sql = "update bi_NewsInfo set News_Times=News_Times+1 where News_Id=" + newsId;
            return CommonBll.ExecuteNonQuery(sql, pDataBase);
        }

        /// <summary>
        /// 发布文章信息
        /// </summary>
        public static int ReleaseNewsInfo(string News_IdList, string pFlag, string pDataBase)
        {
            string sqlStr = "UPDATE dbo.bi_NewsInfo SET News_Flag = replace(News_Flag,'" + pFlag + "','')+'" + pFlag + "' where News_Id in(" + News_IdList + ")";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }
    }
}
